from app import db
from datetime import datetime

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False, comment='用户名')
    password = db.Column(db.String(255), nullable=False, comment='密码(加密存储)')
    email = db.Column(db.String(100), nullable=False, comment='邮件地址')
    status = db.Column(db.Integer, nullable=False, default=1, comment='用户状态(0:禁用,1:正常,2:未激活)')
    background_image = db.Column(db.String(500), nullable=True, comment='背景图片URL')
    avatar = db.Column(db.String(500), nullable=True, comment='头像URL')
    user_group = db.Column(db.String(50), nullable=False, default='user', comment='用户组')
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, comment='创建时间')
    created_by = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=True, comment='创建人ID')
    updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    updated_by = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=True, comment='更新人ID')
    deleted_at = db.Column(db.DateTime, nullable=True, comment='删除时间(软删除)')
    deleted_by = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=True, comment='删除人ID')
    
    # Define indexes
    __table_args__ = (
        db.Index('uk_username', username, unique=True),
        db.Index('uk_email', email, unique=True),
        db.Index('idx_status', status),
        db.Index('idx_user_group', user_group),
        db.Index('idx_deleted_at', deleted_at),
    )
    
    # Self-referential relationships for audit fields
    creator = db.relationship('User', remote_side=[id], foreign_keys=[created_by], backref='created_users')
    updater = db.relationship('User', remote_side=[id], foreign_keys=[updated_by], backref='updated_users')
    deleter = db.relationship('User', remote_side=[id], foreign_keys=[deleted_by], backref='deleted_users')
    
    def __repr__(self):
        return f'<User {self.username}>'